package com.springboot.jpa;


import com.springboot.domain.BtSkills;
import com.springboot.dto.BankListDTO;
import com.springboot.dto.EnterpriseListDTO;
import com.springboot.dto.PersonListSkillDTO;
import com.springboot.repository.PersonRepository;
import com.springboot.repository.PersonSkillsRepository;
import com.springboot.repository.SkillsRepository;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.util.List;

@Service
@Transactional
public class BankJPA {

    private EntityManagerFactory emf;
    private final PersonRepository personRepository;
    private final SkillsRepository skillsRepository;
    private final PersonSkillsRepository personSkillsRepository;

    @Autowired
    public BankJPA(EntityManagerFactory emf,PersonRepository personRepository,SkillsRepository skillsRepository,PersonSkillsRepository personSkillsRepository) {
        this.emf = emf;
        this.personRepository=personRepository;
        this.skillsRepository=skillsRepository;
        this.personSkillsRepository=personSkillsRepository;
    }

    public Page<EnterpriseListDTO> getEnterPriseList(String status, Pageable pageable) {
        EntityManager em = emf.createEntityManager();
        String sql = "SELECT be.id,be.name,be.legal_person AS legalPerson,be.license,be.contact,be.tel,be.email,be.province,be.address," +
                " be.industry,be.establish_time AS establishTime,be.create_time AS createTime,be.status,be.enterprise_profile AS enterpriseProfile," +
                " be.employee_num AS employeeNum,be.views,bf.id AS pictureId FROM bt_enterprise be LEFT JOIN  bt_file bf ON be.id = bf.user_id AND " +
                " bf.parameter_name='headPicture'  WHERE be.status =?1  ORDER BY be.create_time DESC ";
        Query query = em.createNativeQuery(sql);
        query.setParameter(1, status);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(EnterpriseListDTO.class));
        int start = pageable.getOffset();
        int end = (start + pageable.getPageSize()) > query.getResultList().size() ? query.getResultList().size() : (start + pageable.getPageSize());
        return new PageImpl<EnterpriseListDTO>(query.getResultList().subList(start,end), pageable, query.getResultList().size());
    }
    public Page<PersonListSkillDTO> getPersonList(String status, Pageable pageable) {
        EntityManager em = emf.createEntityManager();
        String sql = "select bp.id,bp.name,bp.sex,bp.highest_education as highestEducation,bp.school,bp.profession,bp.working_years as workingYears,bp.post_experience as postExperience," +
                "bp.salary,bp.email,bp.tel,bp.skills_mastery as skillsMastery,bp.create_time as createTime,bp.status,bp.province, bp.address,bp.personnel_level as personnelLevel," +
                "bp.personal_profile as personalProfile,bp.views,bf.id as pictureId from bt_person bp LEFT JOIN  bt_file bf ON bp.id = bf.user_id AND bf.parameter_name='headPicture'" +
                " WHERE bp.status =?1  ORDER BY bp.create_time DESC ";
        Query query = em.createNativeQuery(sql);
        query.setParameter(1, status);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(PersonListSkillDTO.class));
        List<PersonListSkillDTO> personSkills=query.getResultList();
        for(PersonListSkillDTO person:personSkills){
            List<Integer> skillIds = personSkillsRepository.findSkillsByUserId(person.getId());
            List<BtSkills> skills = skillsRepository.findAllByIdIn(skillIds);
            person.setSkills(skills);
        }
        int start = pageable.getOffset();
        int end = (start + pageable.getPageSize()) >personSkills.size() ?personSkills.size() : (start + pageable.getPageSize());
        return new PageImpl<PersonListSkillDTO>(personSkills.subList(start,end), pageable, personSkills.size());
    }
    public Page<BankListDTO> getBankList(String status, Pageable pageable) {
        EntityManager em = emf.createEntityManager();
        String sql = "select bb.id,bb.name,bb.type,bb.license,bb.principal,bb.contact,bb.tel,bb.email,bb.province,bb.address,bb.establish_time as establishTime,bb.create_time as createTime," +
                "bb.status,bb.introduction,bb.views,bf.id as pictureId from bt_bank bb LEFT JOIN  bt_file bf ON bb.id = bf.user_id AND bf.parameter_name='headPicture'" +
                " WHERE bb.status =?1 ORDER BY bb.create_time DESC";
        Query query = em.createNativeQuery(sql);
        query.setParameter(1, status);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(BankListDTO.class));
        int start = pageable.getOffset();
        int end = (start + pageable.getPageSize()) > query.getResultList().size() ? query.getResultList().size() : (start + pageable.getPageSize());
        return new PageImpl<BankListDTO>(query.getResultList().subList(start,end), pageable, query.getResultList().size());
    }

}
